﻿using QRCodeBluetooth.Helper;
using QRCodeBluetooth.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace QRCodeBluetooth.DAL
{
    public class AppDAL
    {
        public List<AppAuditRecord> QueryAppAuditRecord(AppAuditRecord appAuditRecord)
        {
            string strsql = "SELECT * FROM vi_auditrecord";

            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(appAuditRecord.identityInf))
            {
                sqlwhere += " identityInf='" + appAuditRecord.identityInf + "' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strsql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<AppAuditRecord>.SqlList(strsql);
        }

        public List<AppArea> QueryAreaByOwner(string IdentityInf)
        {
            string strsql = "SELECT * FROM vi_auditrecord WHERE IdentityInf='" + IdentityInf + "' AND  auditStatus=2";
            return SqlHelper<AppArea>.SqlList(strsql);
        }

        public List<AppVoucherInfo> QueryVoucher(AppVoucher appVoucher)
        {
            string strsql = string.Format("SELECT * from voucher_record WHERE auditId IN(SELECT id from auditrecord WHERE identityInf='{0}' AND projectId={1} AND auditStatus=2) AND deviceName='{2}'AND startTime<='{3}' AND '{3}'<=endTime ORDER BY pfloor ASC", appVoucher.IdentityInf, appVoucher.ProjectID, appVoucher.DeviceName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<AppVoucherInfo>.SqlList(strsql);
        }

        public List<AppVoucherInfo> QueryDevice(AppVoucher appVoucher)
        {
            string strsql = string.Format("SELECT DISTINCT(deviceName) from voucher_record WHERE auditId IN(SELECT id from auditrecord WHERE identityInf='{0}' AND projectId={1} AND auditStatus=2)", appVoucher.IdentityInf, appVoucher.ProjectID);
            return SqlHelper<AppVoucherInfo>.SqlList(strsql);
        }

        public int QueryProject(string projectkey)
        {
            string strSql = "SELECT id  FROM project_info WHERE project_key='" + projectkey + "'";
            return SqlHelper<ProjectInfo>.SqlScalar(strSql);
        }

        public bool DeleteVoucher(string DeviceNo, int auditid)
        {
            string strSql = "DELETE FROM voucher_record WHERE deviceNo='" + DeviceNo + "' AND auditId=" + auditid;
            return SqlHelper<ProjectInfo>.SqlExecute(strSql);
        }

        public bool InsertVoucher(string sql)
        {
            return SqlHelper<ProjectInfo>.SqlExecute(sql);
        }

        public bool UpdateAudit(int auditid, int auditstate, int projectid)
        {
            string strSql = string.Format("UPDATE auditrecord SET auditStatus={0} WHERE id={1} AND projectId={2}", auditstate, auditid, projectid);
            return SqlHelper<ProjectInfo>.SqlExecute(strSql);
        }

        public bool DeleteAudit(int auditid, int projectid)
        {
            string strSql = string.Format("DELETE FROM auditrecord WHERE id={0} AND projectId={1};DELETE FROM voucher_record WHERE auditId={0}", auditid, projectid);
            return SqlHelper<ProjectInfo>.SqlExecute(strSql);
        }

        public List<AppAuditRecord> QuerySyncAuditRecord()
        {
            string strsql = "SELECT * FROM vi_auditrecord WHERE syncFlag=0";

            return SqlHelper<AppAuditRecord>.SqlList(strsql);
        }

        public List<AppAuditRecord> QuerySyncAuditRecord(int projectid)
        {
            string strsql = "SELECT * FROM vi_auditrecord WHERE syncFlag=0 AND projectId=" + projectid;

            return SqlHelper<AppAuditRecord>.SqlList(strsql);
        }

        public bool UpdateSyncFlag(string ids)
        {
            string strSql = string.Format("UPDATE auditrecord SET syncFlag=1 WHERE id in ({0})", ids);
            return SqlHelper<ProjectInfo>.SqlExecute(strSql);
        }
    }
}
